<?php

require_once('../../connection/_dbconnect_.php');
require_once('m_activity_log.php');

function getUserSchedule($user,$scheduleId){
	
	$parentArray = array();
	
	$sql = "SELECT u.iduser AS username, 
				   s.idschedule AS scheduleID, 
				   s.idshift_schedule AS shift, 
				   s.idqueue_shift AS queueShift, 
				   s.idweekday AS weekdayId,
				   w.name as weekdayName "
    . "FROM user u "
    . "JOIN user_schedule us ON ( u.iduser = us.iduser ) "
    . "JOIN schedule s ON ( us.idschedule = s.idschedule ) "
    . "JOIN weekday w ON ( s.idweekday = w.idweekday ) "
    . "WHERE u.iduser = \"". $user . "\" AND s.idschedule =\"".$scheduleId."\"";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else {
		while($obj = mysql_fetch_object($result)){
			$parentArray[] = $obj;	
		}	
		
		return $parentArray;
	}
}

function getSchedule($user){
	
	$parentArray = array();
	
	
	$sql = "SELECT u.iduser AS username, s.idschedule AS scheduleID, ss.name AS shift, qs.idqueue_shift AS queueShift, w.name AS \n"
    . "DAY \n"
    . "FROM user u\n"
    . "JOIN user_schedule us ON ( u.iduser = us.iduser ) \n"
    . "JOIN schedule s ON ( us.idschedule = s.idschedule ) \n"
    . "JOIN shift_schedule ss ON ( s.idshift_schedule = ss.idshift_schedule ) \n"
    . "JOIN queue_shift qs ON ( s.idqueue_shift = qs.idqueue_shift ) \n"
    . "JOIN weekday w ON ( s.idweekday = w.idweekday ) \n"
    . "WHERE u.iduser = \"". $user . "\""
	. "ORDER BY w.idweekday ASC";
	
		
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else {
		while($obj = mysql_fetch_object($result)){
			$childArray = array();	
			$childArray[] = $obj->username;
			$childArray[] = $obj->scheduleID;
			$childArray[] = $obj->shift;
			$childArray[] = $obj->queueShift;
			$childArray[] = $obj->DAY;
			
			$parentArray[]= $childArray;		
		}
		
		return $parentArray;
	}
}

function getSchedules(){
	
	$parentArray = array();
	
	$sql = "select idschedule,name from schedule";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else {
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->idschedule;
			$childArray[] = $obj->name;
			
			$parentArray[]= $childArray;
		}

		return $parentArray;
	}
}

function addSchedule($newSchedule){
	
	$sql = "insert into schedule (idschedule, name) values (NULL, '".$newSchedule."');";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) {
		return false;
	} else {
		$date =new DateTime('NOW');		
		$result=addLogEntry($date->format('Y-m-d'), "Schedule= ".$newSchedule." was created");	
		return true;
	}
}

function addScheduleUser($user,$weekday,$shift,$qshift){
	
	$sql = "SELECT idschedule FROM `schedule` WHERE `idshift_schedule` =".$shift."
									 AND `idqueue_shift` LIKE '".$qshift."'
									 AND `idweekday` = ".$weekday;	

	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;		
	else if(mysql_num_rows($result)==0) {

		$sql2 = "INSERT INTO `db_proyecto_progra4`.`schedule` (`idschedule`, 
															  `idshift_schedule`, 
															  `idqueue_shift`, 
															  `idweekday`
															  ) VALUES (NULL, 
																		'".$shift."',
																		'".$qshift."',
																		'".$weekday."'
																		);";
																		
		$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		if(!$result)
			return false;
		else {
			
			$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
			
			$obj = mysql_fetch_object($result);
	
			$sql = "INSERT INTO `db_proyecto_progra4`.`user_schedule` (`idschedule`,
																	   `iduser`
																	   ) VALUES ('".$obj->idschedule."', 
																				 '".$user."');";
																				
			$resultRelation = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());	

			if (!$resultRelation) {
				return false;	
			} else {
				$date =new DateTime('NOW');		
				$result=addLogEntry($date->format('Y-m-d'), "Schedule for user=".$user." schedule= ".$obj->idschedule." was created");	
				return true;
			}			
		}
				
	} else {	

		$obj = mysql_fetch_object($result);
	
		$sql = "INSERT INTO `db_proyecto_progra4`.`user_schedule` (`idschedule`,
																   `iduser`
																   ) VALUES ('".$obj->idschedule."', 
																			 '".$user."');";
																			
		$resultRelation = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());	

		if (!$resultRelation) 
			return false;	
		else
			return true;
	}
}

function saveScheduleUser($user,$schedule,$weekday,$shift,$qshift){
	
	$sql = "DELETE FROM `db_proyecto_progra4`.`user_schedule` \n
				 WHERE `user_schedule`.`idschedule` = ".$schedule." \n
				 AND `user_schedule`.`iduser` = '".$user."' \n
				 LIMIT 1;";	

	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;		
	else {
		
		$sql = "SELECT idschedule FROM `schedule` WHERE `idshift_schedule` =".$shift."
									 AND `idqueue_shift` LIKE '".$qshift."'
									 AND `idweekday` = ".$weekday;	
		
		$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
		
		if (!$result) 
			return false;			
		else if(mysql_num_rows($result)==0) {

			$sql2 = "INSERT INTO `db_proyecto_progra4`.`schedule` (`idschedule`, 
																  `idshift_schedule`, 
																  `idqueue_shift`, 
																  `idweekday`
																  ) VALUES (NULL, 
																			'".$shift."',
																			'".$qshift."',
																			'".$weekday."'
																			);";
			
			$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
			
			if(!$result)
				return false;
			else {
				
				$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
				
				if (!$result) 
					return false;	
				else {

					$obj = mysql_fetch_object($result);
					
					$sql2 = "INSERT INTO `db_proyecto_progra4`.`user_schedule` (`idschedule`,
																			   `iduser`
																			   ) VALUES ('".$obj->idschedule."', 
																						 '".$user."');";	
					$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
					
					if(!$result)
						return false;
					else 	
						return true;
				}
			}													
		} else {
			
				$obj = mysql_fetch_object($result);
		
				$sql = "INSERT INTO `db_proyecto_progra4`.`user_schedule` (`idschedule`,
																		   `iduser`
																		   ) VALUES ('".$obj->idschedule."', 
																					 '".$user."');";
																					
				$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());	

				if (!$result) 
					return false;	
				else
					return true;			
		}
	}			
}

function saveScheduleEdit($idschedule, $new_name){
	
	$sql = "update schedule set name='".$new_name."' where idschedule='".$idschedule."';";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) {
		return false;
	} else {
		$date =new DateTime('NOW');		
		$result=addLogEntry($date->format('Y-m-d'), "Schedule= ".$idschedule." was modified");	
		return true;
	}
}

function deleteUserSchedule($user,$scheduleId){
	
	$sql = "DELETE FROM `db_proyecto_progra4`.`user_schedule` 
			WHERE `user_schedule`.`idschedule` = ".$scheduleId." 
			AND `user_schedule`.`iduser` = '".$user."' 
			LIMIT 1;";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) {
		return false;
	} else {
		$date =new DateTime('NOW');		
		$result=addLogEntry($date->format('Y-m-d'), "Schedule=".$scheduleId." was deleted");	
		return true;
	}
}

function loadWeekdays(){
	$parentArray = array();
	
	$sql = "select idweekday,name from weekday";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else {
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->idweekday;
			$childArray[] = $obj->name;
			$parentArray[]= $childArray;	
			
		}
		return $parentArray;	
	}
}

function loadShifts(){
	$parentArray = array();
	
	$sql = "select idshift_schedule,name from shift_schedule";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	if (!$result) 
		return false;
	else {	
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->idshift_schedule;
			$childArray[] = $obj->name;
			$parentArray[]= $childArray;			
		}
		return $parentArray;	
	}
}

function loadQShifts(){
	$parentArray = array();
	
	$sql = "select idqueue_shift,description from queue_shift";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else {	
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->idqueue_shift;
			$childArray[] = $obj->description;
			$parentArray[]= $childArray;			
		}
		return $parentArray;	
	}
}



?>